﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using System.Collections;
using OpalisConsole.Web.User_Imaging;
using System.DirectoryServices.AccountManagement;
using System.DirectoryServices;
using System.DirectoryServices.ActiveDirectory;
using System.Management;
using Microsoft.Win32;
using System.Net;
using System.Net.NetworkInformation;
using System.Text;
using System.ComponentModel;
using smsclictr.automation;

namespace OpalisConsole.Web.Services
{
    /// <summary>
    /// Summary description for EndUserService
    /// </summary>
    [WebService(Namespace = "http://opalis.genmills.com/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class EndUserService : System.Web.Services.WebService
    {
        private static string USER_IMAGING_SQL_CONNECTION_STRING = "Data Source=MGODBOMDP1.genmills.com;Initial Catalog=UserImaging;Integrated Security=SSPI";
        private static string SCCM_SQL_CONNECTION_STRING = "Data Source=MGOSCCCSP1.genmills.com;Initial Catalog=SMS_GMS;Integrated Security=SSPI";

        #region requestMethods
        [WebMethod]
        public MachineData getMachineData(String machineName)
        {
            MachineData retData = new MachineData();
            DateTime TimeStamp = DateTime.MinValue;
            SqlConnection connection = new SqlConnection(SCCM_SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "select vcs.NumberOfProcessors0, vcs.TimeStamp, mem.TotalPhysicalMemory0, vrs.User_Name0, vcs.Manufacturer0, vcs.Model0\n" +
                                      "from v_R_SYSTEM vrs\n" +
                                      "inner join v_GS_x86_PC_Memory mem on mem.ResourceID = vrs.ResourceID\n"+
                                      "inner join v_GS_COMPUTER_SYSTEM vcs on vcs.ResourceID = vrs.ResourceID\n"+
                                      "where vrs.Name0 = @ComputerName";

                command.Parameters.AddWithValue("@ComputerName",machineName);

                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    if (((DateTime)reader[1]).CompareTo(TimeStamp) > 0)
                    {
                        TimeStamp = (DateTime)reader[1];
                        retData = new MachineData()
                        {
                            MachineType = reader[4].ToString() + " " + reader[5].ToString(),
                            TopConsoleUser = reader[3].ToString(),
                            ProcessorNumber = reader[0].ToString(),
                            RAMAmount = Math.Round(Convert.ToDouble(reader[2]) / 1048576).ToString() + " GB"
                        };
                    }
                }
            }
            catch { throw; }
            finally { connection.Close(); }
            return retData;
        }
        [WebMethod]
        public List<AuthorizationRequest> getPendingRequests()
        {
            List<AuthorizationRequest> retList = new List<AuthorizationRequest>();

            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "Select ComputerName, ApprovalRequestID, UserID, Status, EnteredTime, LastUpdated, LastUpdateUser from ApprovalTable where Status NOT LIKE 'Closed'";

                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    String ADSite = String.Empty;
                    SqlConnection SCCMConnection = new SqlConnection(SCCM_SQL_CONNECTION_STRING);

                    try
                    {
                        SqlCommand SCCMCommand = new SqlCommand();
                        SCCMCommand.Connection = SCCMConnection;
                        SCCMConnection.Open();
                        SCCMCommand.CommandText = "Select AD_SITE_NAME0 from V_R_System where Name0=@SystemName";

                        SCCMCommand.Parameters.AddWithValue("@SystemName", reader["ComputerName"].ToString());
                        SqlDataReader CMReader = SCCMCommand.ExecuteReader();



                        while (CMReader.Read())
                        {
                            ADSite = CMReader[0].ToString();
                            break;
                        }
                    }
                    catch { }
                    finally { SCCMConnection.Close(); }
                    string DisplayName = reader["UserID"].ToString();
                    try
                    {
                        DisplayName = getUserFriendlyName(reader["UserID"].ToString(), "genmills.com");
                        if (DisplayName.Equals(string.Empty)) { DisplayName = getUserFriendlyName(reader["UserID"].ToString(), "europe.genmills.com"); }
                        if (DisplayName.Equals(string.Empty)) { DisplayName = getUserFriendlyName(reader["UserID"].ToString(), "asia.genmills.com"); }
                        if (DisplayName.Equals(string.Empty)) { DisplayName = getUserFriendlyName(reader["UserID"].ToString(), "china.genmills.com"); }
                        if (DisplayName.Equals(string.Empty)) { DisplayName = getUserFriendlyName(reader["UserID"].ToString(), "australia.genmills.com"); }
                        if (DisplayName.Equals(string.Empty)) { DisplayName = getUserFriendlyName(reader["UserID"].ToString(), "africa.genmills.com"); }
                        if (DisplayName.Equals(string.Empty)) { DisplayName = getUserFriendlyName(reader["UserID"].ToString(), "india.genmills.com"); }
                    }
                    catch { }                   

                    String machineName = String.Empty;
                    String UserName = String.Empty;
                    String Status = String.Empty;
                    DateTime EnteredTime = DateTime.MinValue;
                    DateTime LastUpdatedTime = DateTime.MinValue;
                    String LastUpdateUser = String.Empty;
                    String ApprovalID = String.Empty;   
                    try { machineName = reader["ComputerName"].ToString(); } catch{}
                    try { UserName = reader["UserID"].ToString(); } catch{}
                    try { Status = reader["Status"].ToString(); } catch{}
                    try { EnteredTime = Convert.ToDateTime(reader["EnteredTime"]); } catch{}
                    try { LastUpdatedTime = Convert.ToDateTime(reader["LastUpdated"]); } catch{}
                    try { LastUpdateUser = reader["LastUpdateUser"].ToString(); } catch{}
                    try { ApprovalID = reader["ApprovalRequestID"].ToString(); }
                    catch { }

                    retList.Add(new AuthorizationRequest()
                    {
                        MachineName = machineName,
                        DisplayName = DisplayName,
                        UserName = UserName,
                        Status = Status,
                        ADSite = ADSite,
                        EnteredTime = EnteredTime,
                        LastUpdatedTime = LastUpdatedTime,
                        LastUpdateUser = LastUpdateUser,
                        ApprovalID = ApprovalID

                    });
                }
            }
            catch { throw; }
            finally { connection.Close(); }

            return retList;
        }
        [WebMethod]
        public bool authenticateUser(String username, String password, String domain)
        {
            using(PrincipalContext pc = new PrincipalContext(ContextType.Domain, domain))
            {
                bool isValid = pc.ValidateCredentials(username, password);
                return isValid;
            }
        }
        [WebMethod]
        public List<String> getADSites()
        {
            SqlConnection connection = new SqlConnection(SCCM_SQL_CONNECTION_STRING);
            List<String> retValue = new List<String>();

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "select distinct AD_Site_Name0 from v_r_system";

                connection.Open();

                SqlDataReader reader = command.ExecuteReader();             

                while (reader.Read())
                {
                    retValue.Add(reader["AD_Site_Name0"].ToString());
                }
            }
            catch { throw; }
            finally { connection.Close(); }

            return retValue;
        }
        [WebMethod]
        public bool checkGroupMembership(String userName, String groupName, String domain)
        {
            string userDN = GetObjectDistinguishedName(objectClass.user, returnType.distinguishedName, userName);
            if (userDN != null)
            {
                ArrayList groups = getGroups(userDN, true);
                foreach (string group in groups)
                {
                    if (group.ToUpper().Contains(groupName.ToUpper()))
                    {
                        return true;
                    }
                }
            }
            return false;
            /*
            string connectionPrefix = "LDAP://" + domain;
            DirectoryEntry ent = new DirectoryEntry(connectionPrefix);

            DirectorySearcher mySearcher = new DirectorySearcher(ent);

            mySearcher.Filter = "(&(objectClass=user)(|(cn=" + userName + ")(sAMAccountName=" + userName + ")))";

            SearchResult result = mySearcher.FindOne();

            if (result == null) { return false; }

            DirectoryEntry directoryObject = result.GetDirectoryEntry();

            PropertyValueCollection valueCollection = directoryObject.Properties["memberOf"];
            IEnumerator en = valueCollection.GetEnumerator();

            while (en.MoveNext())
            {
                if (en.Current != null)
                {
                    if(en.Current.ToString().Contains(groupName)) { return true; }
                }
            }
            return false;
             */
        }
        [WebMethod]
        public void updateOSDSetting(String groupName, String setting, String value)
        {
            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "Select * from OSDSettingsGroupDefinition where OSDSettingGroupName = @OSDSettingGroupName";
                command.Parameters.AddWithValue("@OSDSettingGroupName", groupName);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                //Record for this group already exists
                if (reader.HasRows)
                {
                    reader.Close();
                    command = new SqlCommand();
                    command.Connection = connection;
                    command.CommandText = "UPDATE OSDSettingsGroupDefinition SET " + setting + " = @Value WHERE OSDSettingGroupName = @OSDSettingGroupName";
                    command.Parameters.AddWithValue("@OSDSettingGroupName", groupName);
                    command.Parameters.AddWithValue("@Value", value);

                    command.ExecuteNonQuery();
                }
                //New Record
                else
                {
                    reader.Close();
                    command = new SqlCommand();
                    command.Connection = connection;
                    command.CommandText = "INSERT INTO OSDSettingsGroupDefinition (OSDSettingGroupID, OSDSettingGroupName, " + setting + ") VALUES(NEWID(), @OSDSettingGroupName, @Value)";
                    command.Parameters.AddWithValue("@OSDSettingGroupName", groupName);
                    command.Parameters.AddWithValue("@Value", value);
                    
                    command.ExecuteNonQuery();
                }
                
            }
            catch
            { throw; }
            finally { connection.Close(); }
        }
        [WebMethod]
        public OSDSettings getOSDSettings(String groupName)
        {
            OSDSettings settings = new OSDSettings();

            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "select OSDSettingGroupID, OSDDataYN, OSDDomainName, OSDDomainOUName, OSDInputLocale, OSDNotifyEmail, OSDNotifyEmailCC, OSDSystemLocale, " +
                                      "OSDTimeZone, OSDUILanguage, OSDUserLocale, OSDMBID, OSDOS, OSDTechID, OSDNetworkJoinType, OSDDeploymentType, SCCMSiteCode from " + 
                                      "OSDSettingsGroupDefinition where OSDSettingGroupName = @GroupName";

                command.Parameters.AddWithValue("@GroupName", groupName);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    settings = new OSDSettings()
                    {
                        OSDDataYN = reader["OSDDataYN"].ToString(),
                        OSDDomainName = reader["OSDDomainName"].ToString(),
                        ODSDomainOUName = reader["OSDDomainOUName"].ToString(),
                        OSDInputLocale = reader["OSDInputLocale"].ToString(),
                        OSDNotifyEmail = reader["OSDNotifyEmail"].ToString(),
                        OSDNotifyEmailCC = reader["OSDNotifyEmailCC"].ToString(),
                        OSDSystemLocale = reader["OSDSystemLocale"].ToString(),
                        OSDTimeZone = reader["OSDTimeZone"].ToString(),
                        OSDUILanguage = reader["OSDUILanguage"].ToString(),
                        OSDUserLocale = reader["OSDUserLocale"].ToString(),
                        OSDMBID = reader["OSDMBID"].ToString(),
                        OSDOS = reader["OSDOS"].ToString(),
                        OSDTechID = reader["OSDTechID"].ToString(),
                        OSDNetworkJoinType = reader["OSDNetworkJoinType"].ToString(),
                        OSDDeploymentType = reader["OSDDeploymentType"].ToString(),
                        SCCMSiteCode = reader["SCCMSiteCode"].ToString()
                    };
                }
            }
            catch { throw; }
            finally { connection.Close(); }

            return settings;
        }        
        [WebMethod]
        public List<EntriesPerDate> getEntriesPerDay(String MinuteOffset)
        {
            List<EntriesPerDate> entriesPerDay = new List<EntriesPerDate>();

            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);
            
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "select  COUNT(cast(CONVERT(varchar(8), DateAdd(MINUTE, " + MinuteOffset + ", StartDateTime), 112) AS datetime)), " +
                                      "cast(CONVERT(varchar(8), DateAdd(MINUTE, " + MinuteOffset + ", StartDateTime), 112) AS datetime)  " +
                                      "from ComputerImaging where (Status = 'Scheduled' or Status = 'New') " +
                                      "group by cast(CONVERT(varchar(8), DateAdd(MINUTE, " + MinuteOffset + ", StartDateTime), 112) AS datetime)";

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    entriesPerDay.Add(new EntriesPerDate(){ Day = (DateTime)reader[1], NumberOfScheduled = (int)reader[0] });
                }
            }
            
            catch
            { throw; }
            finally { connection.Close(); }
            
            return entriesPerDay;
        }
        [WebMethod]
        public void createNewImagingRequest(string computerName, string userName, DateTime scheduledDateTime, string Computer_ID, string userID, string OSDDATAYN)
        {
            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "INSERT INTO ComputerImaging (Imaging_ID, Computer_ID, OSDComputerName, Status, StartDateTime, OSDEUID, OSDDATAYN) VALUES (NEWID(), @Computer_ID, @OSDComputerName, @Status, @Start_Date, @OSDEUID, @OSDDATAYN)";
                command.Parameters.AddWithValue("@Computer_ID", Computer_ID);
                command.Parameters.AddWithValue("@OSDComputerName", computerName);
                command.Parameters.AddWithValue("@Status", "New");
                command.Parameters.AddWithValue("@Start_Date", scheduledDateTime);
                command.Parameters.AddWithValue("@OSDEUID", userID);
                command.Parameters.AddWithValue("@OSDDATAYN", OSDDATAYN);
                connection.Open();
                command.ExecuteNonQuery();
            }
            catch
            { throw; }
            finally { connection.Close(); }
        }
        [WebMethod]
        public List<DateTime> getOpenTimeSlots(DateTime Day, int maxPerHour)
        {
            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            List<DateTime> openSlots = new List<DateTime>();

            for (int i = 0; i < 24; i++)
            {
                if (Day > DateTime.Now)
                {
                    try
                    {
                        SqlCommand command = new SqlCommand();
                        command.Connection = connection;
                        command.CommandText = "Select Count (*) from ComputerImaging where (Status = 'New' or Status = 'Scheduled') and StartDateTime = @Start_Date";
                        command.Parameters.AddWithValue("@Start_Date", Day);

                        connection.Open();
                        SqlDataReader reader = command.ExecuteReader();

                        while (reader.Read())
                        {
                            if ((int)reader[0] < maxPerHour)
                            {
                                openSlots.Add(Day);
                            }
                        }
                    }
                    catch
                    { throw; }
                    finally
                    {
                        connection.Close();
                    }
                }
                Day = Day.AddHours(1);
            }

            return openSlots;
        }
        [WebMethod]
        public void submitApprovalRequest(String ComputerName, String userID)
        {
            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "INSERT INTO ApprovalTable (ApprovalRequestID, ComputerName, UserID, Status, EnteredTime) VALUES (NEWID(), @ComputerName, @UserID, @Status, @EnteredTime)";
                command.Parameters.AddWithValue("@ComputerName",ComputerName);
                command.Parameters.AddWithValue("@UserID", userID);
                command.Parameters.AddWithValue("@Status", "New");
                command.Parameters.AddWithValue("@EnteredTime", DateTime.UtcNow);
                connection.Open();
                command.ExecuteNonQuery();
            }
            catch { throw; }    

            finally
            {
                connection.Close();
            }
        }
        [WebMethod]
        public String getApprovalRequestStatus(String ComputerName, String userID)
        {
            String retValue = String.Empty;

            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "select Status from ApprovalTable where ComputerName = @ComputerName and UserID = @UserID and Status NOT LIKE 'Closed'";

                command.Parameters.AddWithValue("@ComputerName", ComputerName);
                command.Parameters.AddWithValue("@UserID", userID);

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                
                while (reader.Read())
                {
                    retValue = reader["Status"].ToString();
                }
            }
            catch { throw; }
            finally
            {
                connection.Close();
            }

            return retValue;
        }
        [WebMethod]
        public String getComputerName(string IPAddr)
        {
            String retValue = "Not Found";
           
            ManagementObjectSearcher searcher = new ManagementObjectSearcher(new ManagementScope("\\\\" + IPAddr + "\\root\\CIMV2"),
                                                                                 new SelectQuery("Select Name from win32_ComputerSystem"));
            try
            {
                foreach (ManagementObject computerName in searcher.Get())
                {
                    retValue = computerName["Name"].ToString();
                }
            }
            catch { }
            if(retValue.Equals("Not Found"))
            {
                retValue = getComputerNameRegistry(IPAddr);
            }
            return retValue;
        }
        [WebMethod]
        public String getComputerOwnerWithUsername(string ComputerName, string username)
        {
            SqlConnection connection = new SqlConnection(SCCM_SQL_CONNECTION_STRING);
            string id = "Not Found";
            bool validIDMatch = false;
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "Select User_Name0 from v_R_System where Name0 = @ComputerName";
                command.Parameters.AddWithValue("@ComputerName", ComputerName);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    if (id.Equals("Not Found") && (!reader["User_Name0"].ToString().Equals("")))
                    {
                        id = reader["User_Name0"].ToString();
                        if (id.ToUpper().Equals(username.ToUpper())) { validIDMatch = true; }
                    }
                }
            }
            catch { return id; }
            finally { connection.Close(); }

            if (id.Equals("Not Found") || !validIDMatch)
            {
                try
                {
                    String DN = GetObjectDistinguishedName(objectClass.computer, returnType.distinguishedName, ComputerName);
                    String ManagedBy = AttributeValuesSingleString("ManagedBy", DN);
                    if (!ManagedBy.Equals("Not Found"))
                    {
                        Char[] Seperators = { '=', ',' };
                        ManagedBy = ManagedBy.Split(Seperators)[1];
                        DN = GetObjectDistinguishedName(objectClass.user, returnType.distinguishedName, ManagedBy);
                        String UserID = AttributeValuesSingleString("sAMAccountName", DN);
                        if (!UserID.Equals("Not Found")) { id = UserID; }
                    }
                }
                catch { }
            }
            
            return id;
        }
        [WebMethod]
        public String getComputerOwner(string ComputerName)
        {
            SqlConnection connection = new SqlConnection(SCCM_SQL_CONNECTION_STRING);
            string id = "Not Found";
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "Select User_Name0 from v_R_System where Name0 = @ComputerName";
                command.Parameters.AddWithValue("@ComputerName", ComputerName);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    if (id.Equals("Not Found") && (!reader["User_Name0"].ToString().Equals("")))
                    {
                        id = reader["User_Name0"].ToString();
                    }
                }
            }
            catch { return id; }
            finally { connection.Close(); }

            if (id.Equals("Not Found"))
            {
                try
                {
                    String DN = GetObjectDistinguishedName(objectClass.computer, returnType.distinguishedName, ComputerName);
                    String ManagedBy = AttributeValuesSingleString("ManagedBy", DN);
                    if (!ManagedBy.Equals("Not Found"))
                    {
                        Char[] Seperators = { '=', ',' };
                        ManagedBy = ManagedBy.Split(Seperators)[1];
                        DN = GetObjectDistinguishedName(objectClass.user, returnType.distinguishedName, ManagedBy);
                        String UserID = AttributeValuesSingleString("sAMAccountName", DN);
                        if (!UserID.Equals("Not Found")) { id = UserID; }
                    }
                }
                catch { }
            }

            return id;
        }
        [WebMethod]
        public void updateImagingRequest(string imagingID, DateTime scheduledDateTime, string Status)
        {
            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "UPDATE ComputerImaging SET Status = @Status, StartDateTime = @Start_Date WHERE Imaging_ID = @imagingID";
                command.Parameters.AddWithValue("@imagingID", imagingID);
                command.Parameters.AddWithValue("@Status", Status);
                command.Parameters.AddWithValue("@Start_Date", scheduledDateTime);

                connection.Open();
                command.ExecuteNonQuery();
            }
            catch
            { throw; }
            finally { connection.Close(); }
        }
        [WebMethod]
        public string lookupDNSName(string IPAddr, string UserName)
        {
            SqlConnection connection = new SqlConnection(SCCM_SQL_CONNECTION_STRING);

            string id = "Not Found";

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "Select Name0 from v_R_System where User_Name0 LIKE '" + UserName + "'";

                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                ArrayList MachineList = new ArrayList();

                while (reader.Read())
                {
                    String MachineIP = String.Empty;
                    try
                    {
                        foreach (System.Net.IPAddress address in System.Net.Dns.GetHostEntry(reader[0].ToString()).AddressList)
                        {
                            if (address.ToString().Equals(IPAddr))
                            {
                                id = reader[0].ToString();
                            }
                        }
                    }
                    catch { }
                }
            }
            catch { return id; }
            finally { connection.Close(); }

            return id;
        }
        [WebMethod]
        public string getApprovedComputerID(string computerName)
        {
            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            string id = "Not Approved";

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "Select Computer_ID from ApprovedComputers where Name LIKE '" + computerName + "'";

                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    id = reader[0].ToString();
                }
            }
            catch { throw; }
            finally { connection.Close(); }

            return id;
        }
        [WebMethod]
        public string getRequestStatus(string computerName)
        {
            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            string status = "Not Scheduled";

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "Select Imaging_ID, OSDComputerName, Status, StartDateTime, OSDDATAYN from ComputerImaging where OSDComputerName = @ComputerName and (Status LIKE 'NEW' or Status LIKE 'Scheduled' or Status LIKE 'Executing' or Status LIKE 'Scheduling')";
                command.Parameters.AddWithValue("@ComputerName", computerName);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    status = reader[0].ToString() + "," + reader[1].ToString() + "," + reader[2].ToString() + "," + reader[3].ToString() + "," + reader[4].ToString();
                }
            }
            catch { throw; }
            finally { connection.Close(); }

            return status;
        }
        [WebMethod]
        public void closePendingApprovalRequest(String ComputerName, String UserName, String Status, String Requester, String domain)
        {
            String requesterFriendlyName = getUserFriendlyName(Requester, domain);
            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "UPDATE ApprovalTable SET Status = @Status, LastUpdateUser=@LastUpdateUser, LastUpdated = @LastUpdated Where ComputerName = @ComputerName and UserID = @UserID";
                command.Parameters.AddWithValue("@ComputerName", ComputerName);
                command.Parameters.AddWithValue("@UserID", UserName);
                command.Parameters.AddWithValue("@LastUpdated", DateTime.UtcNow);
                command.Parameters.AddWithValue("@Status", Status);
                command.Parameters.AddWithValue("@LastUpdateUser", requesterFriendlyName);
                
                connection.Open();

                command.ExecuteNonQuery();
            }
            catch { throw; }
            finally { connection.Close(); }
        }
        
        [WebMethod]
        public void AddRejectionMessage(String ComputerName, String UserName, String RejectionMessage)
        {
            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "UPDATE ApprovalTable SET RejectionMessage = @RejectionMessage Where ComputerName = @ComputerName";
                command.Parameters.AddWithValue("@ComputerName", ComputerName);
                command.Parameters.AddWithValue("@RejectionMessage", RejectionMessage);

                connection.Open();

                command.ExecuteNonQuery();
            }
            catch { throw; }
            finally { connection.Close(); }
        }
        [WebMethod]
        public void RemoveComputerFromApproveComptuers(String computerName)
        {
            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "Select * from ApprovedComputers where Name = @ComputerName";
                command.Parameters.AddWithValue("@ComputerName", computerName);

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Close();

                    command = new SqlCommand();
                    command.Connection = connection;
                    command.CommandText = "DELETE FROM ApprovedComputers Where Name = @ComputerName";
                    command.Parameters.AddWithValue("@ComputerName", computerName);

                    command.ExecuteNonQuery();
                }
            }
            catch { throw; }
            finally { connection.Close(); }
        }
        [WebMethod]
        public List<SoftwareData> getSoftwareData(String username, String Domain)
        {
            //Find SCCM Delivered Package Information
            SqlConnection connection = new SqlConnection(SCCM_SQL_CONNECTION_STRING);

            List<SoftwareData> softwareDataList = new List<SoftwareData>();
            Hashtable ht = new Hashtable();
            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "Select distinct ad.CollectionId, vFCM.Name [Member Name], vProg.ProgramName, vosd.MaxVersion, vosd.Platform, Manufacturer, Version, vPack.Name\n" +
                                      "from v_r_user u\n" +
                                      "inner join v_ra_User_UserGroupName uug on u.ResourceID = uug.ResourceID\n" +
                                      "INNER JOIN v_CollectionRuleDirect crd ON uug.User_Group_Name0 = crd.RuleName\n" +
                                      "INNER JOIN dbo.v_Advertisement AS Ad ON crd.CollectionId = Ad.CollectionId\n" +
                                      "INNER JOIN v_Collection AS Col ON crd.CollectionId = Col.CollectionId\n" +
                                      "INNER JOIN v_FullCollectionMembership vFCM on vFCM.CollectionId = Col.CollectionId\n" +
                                      "INNER JOIN v_Package AS vPack on vPack.PackageId = Ad.PackageId\n" +
                                      "INNER JOIN v_Program AS vProg on VProg.PackageId = Ad.PackageId\n" +
                                      "Left Join v_os_Details vosd on vosd.PackageID = vPack.PackageID\n" +
                                      "where\n" +
                                      "Col.Name LIKE 'AD GROUP:%' and\n" +
                                      "vFCM.Name LIKE 'GENMILLS\\GMI_APP_%' and\n" +
                                      "vProg.ProgramName NOT LIKE '*' and\n" +
                                      "u.User_Name0 = @UserName and\n" +
                                      "Ad.ExpirationTimeEnabled <> 2\n" +
                                      "Order By Name, ProgramName\n";


                command.Parameters.AddWithValue("@UserName", username);

                SqlDataReader queryReader = command.ExecuteReader();
                while (queryReader.Read())
                {
                    try
                    {
                        String ADGroupName = queryReader["Member Name"].ToString();
                        String PName = queryReader["Manufacturer"] + " " + queryReader["Name"].ToString() + " " + queryReader["Version"];
                        String Status = "Cannot be Reinstalled";

                        double cost = 0;
                        bool keep = true;

                        //Have not seen this Program Yet
                        if (!ht.Contains(ADGroupName))
                        {
                            bool seen = false;
                            foreach(SoftwareData package in ht.Values)
                            {
                                if (package.PackageName.Equals(PName))
                                {
                                    seen = true;

                                    String status = package.Status.ToString();
                                    switch (status)
                                    {
                                        case "Cannot be Reinstalled":
                                            if (queryReader["ProgramName"].ToString().ToUpper().Contains("VIRTUAL APPLICATION"))
                                            {
                                                package.Status = "Self Install";
                                                package.PackageName = PName;
                                            }
                                            //Not a Virtual Application Package, check version compatibility
                                            else
                                            {
                                                //Check WIN 7 and 64 Bit
                                                if (queryReader["MaxVersion"].ToString().StartsWith("6.") && queryReader["Platform"].ToString().Equals("x64"))
                                                {
                                                    //Check For 'GMI Imaging' Program
                                                    if (queryReader["ProgramName"].ToString().ToUpper().Contains("GMI IMAGING"))
                                                    {
                                                        package.Status = "Automatically Installed";
                                                        package.PackageName = PName;
                                                    }
                                                    //Not a 'GMI Imaging' Program
                                                    else
                                                    {
                                                        package.Status = "Self Install";
                                                        package.PackageName = PName;
                                                    }
                                                }
                                            }
                                            break;
                                        case "Automatically Installed":
                                            break;
                                        case "Self Install":
                                            if (queryReader["ProgramName"].ToString().ToUpper().Contains("GMI IMAGING"))
                                            {
                                                package.Status = "Automatically Installed";
                                                package.PackageName = PName;
                                            }
                                            break;
                                    }
                                    break;
                                }
                            }
                            if (!seen)
                            {
                                if (queryReader["ProgramName"].ToString().ToUpper().Contains("VIRTUAL APPLICATION"))
                                {
                                    Status = "Self Install";
                                }
                                //Not a Virtual Application Package, check version compatibility
                                else
                                {
                                    //Check WIN 7 and 64 Bit
                                    if (queryReader["MaxVersion"].ToString().StartsWith("6.") && queryReader["Platform"].ToString().Equals("x64"))
                                    {
                                        //Check For 'GMI Imaging' Program
                                        if (queryReader["ProgramName"].ToString().ToUpper().Contains("GMI IMAGING"))
                                        {
                                            Status = "Automatically Installed";
                                        }
                                        //Not a 'GMI Imaging' Program
                                        else
                                        {
                                            Status = "Self Install";
                                        }
                                    }
                                }

                                ht.Add(ADGroupName, new SoftwareData() { PackageName = PName, Status = Status, cost = cost, keep = keep });
                            }
                        }
                        //Software Table includes 'program' already
                        else
                        {
                            String status = ((SoftwareData)ht[ADGroupName]).Status.ToString();
                            switch (status)
                            {
                                case "Cannot be Reinstalled":
                                    if (queryReader["ProgramName"].ToString().ToUpper().Contains("VIRTUAL APPLICATION"))
                                    {
                                        ((SoftwareData)ht[ADGroupName]).Status = "Self Install";
                                        ((SoftwareData)ht[ADGroupName]).PackageName = PName;
                                    }
                                    //Not a Virtual Application Package, check version compatibility
                                    else
                                    {
                                        //Check WIN 7 and 64 Bit
                                        if (queryReader["MaxVersion"].ToString().StartsWith("6.") && queryReader["Platform"].ToString().Equals("x64"))
                                        {
                                            //Check For 'GMI Imaging' Program
                                            if (queryReader["ProgramName"].ToString().ToUpper().Contains("GMI IMAGING"))
                                            {
                                                ((SoftwareData)ht[ADGroupName]).Status = "Automatically Installed";
                                                ((SoftwareData)ht[ADGroupName]).PackageName = PName;
                                            }
                                            //Not a 'GMI Imaging' Program
                                            else
                                            {
                                                ((SoftwareData)ht[ADGroupName]).Status = "Self Install";
                                                ((SoftwareData)ht[ADGroupName]).PackageName = PName;
                                            }
                                        }
                                    }
                                    break;
                                case "Automatically Installed":
                                    break;
                                case "Self Install":
                                    if (queryReader["ProgramName"].ToString().ToUpper().Contains("GMI IMAGING"))
                                    {
                                        ((SoftwareData)ht[ADGroupName]).Status = "Automatically Installed";
                                        ((SoftwareData)ht[ADGroupName]).PackageName = PName;
                                    }
                                    break;
                            }
                        }
                    }
                    catch { }
                }
            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }

            //Lookup information in AD Groups for Terminal Services
            string userDN = GetObjectDistinguishedName(objectClass.user, returnType.distinguishedName, username);
            if(userDN != null)
            {
                ArrayList groups = getGroups(userDN, false);
                foreach (string group in groups)
                {
                    if (group.ToUpper().Contains("GMI_APP_TERMINALSERVICES_"))
                    {
                        string appName = AttributeValuesSingleString("gmiCustomAttribute3", "LDAP://" + group);
                        if (!appName.Equals("Not Found"))
                        {
                            bool found = false;
                            foreach (SoftwareData data in ht.Values)
                            {
                                if (data.PackageName.StartsWith(appName))
                                {
                                    data.Status = "Automatically Installed";
                                    data.PackageName = appName;
                                    found = true;
                                }
                            }
                            if (!found)
                            {
                                ht.Add(appName, new SoftwareData() { cost = 0, keep = true, PackageName = appName, Status = "Automatically Installed" });
                            }
                        }
                    }
                }
            }
            foreach (SoftwareData data in ht.Values)
            {
                bool seen = false;
                foreach (SoftwareData tempData in softwareDataList)
                {
                    if(tempData.PackageName.Equals(data.PackageName))
                    {
                        seen = true;
                    }
                }
                if(!seen)
                {
                    if (!data.PackageName.Contains("OneNote"))
                    {
                        softwareDataList.Add(data);
                    }
                }
            }

            return softwareDataList;
        }
        [WebMethod]
        public void addComputerToApproveComptuers(String computerName)
        {
            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "Select * from ApprovedComputers where Name = @ComputerName";
                command.Parameters.AddWithValue("@ComputerName", computerName);

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                if (!reader.HasRows)
                {
                    reader.Close();

                    command = new SqlCommand();
                    command.Connection = connection;
                    command.CommandText = "INSERT INTO ApprovedComputers VALUES (NEWID(), @ComputerName)";
                    command.Parameters.AddWithValue("@ComputerName", computerName);

                    command.ExecuteNonQuery();
                }
            }
            catch { throw; }
            finally { connection.Close(); }
        }
        [WebMethod]
        public List<ApprovedComputer> getAllApprovedRequests()
        {
            List<ApprovedComputer> retList = new List<ApprovedComputer>();
            Hashtable ht = new Hashtable();
            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "select distinct AC.Name, ci.Status, ci.StartDateTime  from ApprovedComputers ac\n" +
                                      "left join ComputerImaging ci on ac.Computer_ID = ci.Computer_ID\n" +
                                      "order by AC.Name asc";

                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    String MachineName = reader["Name"].ToString().ToUpper();
                    if (!ht.ContainsKey(MachineName))
                    {
                        String Status = "Not Scheduled";
                        String UserName = getComputerOwner(MachineName);
                        if (!reader["Status"].ToString().Equals(String.Empty))
                        {
                            Status = reader["Status"].ToString();
                            switch (Status)
                            {
                                case "Success":
                                    Status = "Re-Imaged";
                                    break;
                                case "Scheduled":
                                    Status = "Scheduled";
                                    break;
                                case "Failed":
                                    Status = "Failed";
                                    break;
                                case "":
                                    Status = "Not Scheduled";
                                    break;
                                default:
                                    break;
                            }
                        }
                        ht.Add(reader["Name"].ToString().ToUpper(), new ApprovedComputer() { 
                                                                                                ImagingID = "NULL", 
                                                                                                MachineName = MachineName, 
                                                                                                Status = Status, 
                                                                                                UserName = UserName 
                                                                                            });
                    }
                    else
                    {
                        if (!reader["Status"].ToString().Equals(String.Empty))
                        {
                            String Status = reader["Status"].ToString();
                            switch (Status)
                            {
                                case "Success":
                                    Status = "Re-Imaged";
                                    break;
                                case "Scheduled":
                                    Status = "Scheduled";
                                    break;
                                case "Failed":
                                    Status = "Failed";
                                    break;
                                default:
                                    Status = "Not Scheduled";
                                    break;
                            }
                            switch(((ApprovedComputer)ht[MachineName]).Status)
                            {
                                case "Success":
                                    if (Status.Equals("Scheduled")) { ((ApprovedComputer)ht[MachineName]).Status = Status; }
                                    break;
                                case "Failed":
                                    if (!Status.Equals("Failed")) { ((ApprovedComputer)ht[MachineName]).Status = Status; }
                                    break;
                                default:
                                    break;
                            }
                        }
                    }
                }
                foreach (ApprovedComputer req in ht.Values)
                {
                    retList.Add(req);
                }
            }
            catch { throw; }
            finally { connection.Close(); }

            return retList;
        }
        #endregion
        #region health checks
        [WebMethod]
        public bool checkPingStatus(String ipAddr)
        {
            bool returnStatus = true;
            Ping pingSender = new Ping();

            string data = "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";
            byte[] buffer = Encoding.ASCII.GetBytes(data);

            // Wait 12 seconds for a reply.
            int timeout = 12000;

            // Set options for transmission:
            // The data can go through 64 gateways or routers
            // before it is destroyed, and the data packet
            // cannot be fragmented.
            PingOptions options = new PingOptions(64, true);

            PingReply reply = pingSender.Send(ipAddr, timeout, buffer, options);

            if (reply.Status != IPStatus.Success)
            {
                returnStatus = false;
            }

            return returnStatus;
        }
        [WebMethod]
        public bool checkNotVPNConnectedStatus(String ipAddr)
        {
            ManagementScope scope = new ManagementScope(@"\\" + ipAddr + @"\root\cimv2");
            bool returnStatus = true;
            try
            {
                ManagementObjectSearcher s = new ManagementObjectSearcher((scope), new WqlObjectQuery("SELECT * FROM Win32_NetworkAdapter"));

                foreach (ManagementObject o in s.Get())
                {
                    o.Get();
                    if (o["NetConnectionStatus"] != null)
                    {
                        if (o["NetConnectionStatus"].ToString().Equals("2") && (o["Name"].ToString().ToUpper().Contains("JUNIPER") || o["Name"].ToString().ToUpper().Contains("CISCO")))
                        {
                            returnStatus = false;
                            break;
                        }
                    }
                }
            }
            catch
            {
                returnStatus = false;
            }
            return returnStatus;
        }
        [WebMethod]
        public bool checkWiredConnectionStatus(String ipAddr)
        {
            ManagementScope scope = new ManagementScope(@"\\" + ipAddr + @"\root\cimv2");
            bool returnStatus = true;
            try
            {
                ManagementObjectSearcher s = new ManagementObjectSearcher((scope), new WqlObjectQuery("SELECT * FROM Win32_NetworkAdapter"));

                foreach (ManagementObject o in s.Get())
                {
                    o.Get();
                    if (o["NetConnectionStatus"] != null)
                    {
                        if (o["NetConnectionStatus"].ToString().Equals("2"))
                        {
                            string adapterName = o["Name"].ToString();
                            string adapterType = o["AdapterType"].ToString();
                            if (adapterName.ToUpper().Contains("WIRELESS") || adapterName.ToUpper().Contains("-N"))
                            {
                                returnStatus = false;
                                break;
                            }
                        }
                    }
                }
            }
            catch
            {
                returnStatus = false;
            }
            return returnStatus;
        }
        
        [WebMethod]
        public bool checkACPoweredStatus(String ipAddr)
        {
            ManagementScope scope = new ManagementScope(@"\\" + ipAddr + @"\root\cimv2");
            bool returnStatus = true;
            try
            {
                ManagementObjectSearcher s = new ManagementObjectSearcher((scope), new WqlObjectQuery("SELECT * FROM Win32_Battery"));

                foreach (ManagementObject o in s.Get())
                {
                    o.Get();
                    if (o["BatteryStatus"] != null)
                    {
                        if (o["BatteryStatus"].ToString().Equals("1"))
                        {
                            returnStatus = false;
                            break;
                        }
                    }
                }
            }
            catch
            {
                returnStatus = false;
            }
            return returnStatus;
        }
        [WebMethod]
        public bool checkSCCMClientHealthStatus(String MachineName)
        {
            bool returnStatus = true;
            try
            {
                SMSClient targetClient = new SMSClient(MachineName);

                try { int componentcount = targetClient.Components.CCM_InstalledComponent.Count; if (componentcount != 16) { returnStatus = false; } }
                catch { }
                try { string mp = targetClient.ManagementPoint; if (mp.Equals(string.Empty)) { returnStatus = false; } }
                catch { }
                try { string sitecode = targetClient.SiteCode; if (sitecode.Equals(string.Empty)) { returnStatus = false; } }
                catch { }
                try { string cofigmanID = targetClient.ClientId; if (cofigmanID.Equals(string.Empty)) { returnStatus = false; } }
                catch { }

                targetClient.StartSMSAgent();
            }
            catch (UnauthorizedAccessException)
            {
                returnStatus = true;
            }
            catch (ManagementException e)
            {

                return returnStatus = true;
            }
            catch (Exception e)
            {
                
            }
            return returnStatus;
        }
        [WebMethod]
        public bool checkRemotePowerPlan(String MachineName)
        {
            bool returnStatus = false;
            String preferredPlan = String.Empty;
            try
            {
                RegistryKey baseKey = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, MachineName, RegistryView.Registry32);
                RegistryKey preferredPlanKey = baseKey.OpenSubKey(@"SOFTWARE\Microsoft\Windows\CurrentVersion\explorer\ControlPanel\NameSpace\{025A5937-A6BE-4686-A844-36FE4BEC8B6D}");
                preferredPlan = preferredPlanKey.GetValue("PreferredPlan").ToString();
                preferredPlanKey.Close(); 

                if (preferredPlan.Equals("8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c")) { returnStatus = true; }
                else
                {
                    RegistryKey baseKey64 = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, MachineName, RegistryView.Registry64);
                    RegistryKey preferredPlanKey64 = baseKey64.OpenSubKey(@"SOFTWARE\Microsoft\Windows\CurrentVersion\explorer\ControlPanel\NameSpace\{025A5937-A6BE-4686-A844-36FE4BEC8B6D}");
                    preferredPlan = preferredPlanKey64.GetValue("PreferredPlan").ToString();
                    preferredPlanKey64.Close();

                    if (preferredPlan.Equals("8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c")) { returnStatus = true; }
                }
                
            }
            catch
            {
                returnStatus = false;
            }

            return returnStatus;
        }
        [WebMethod]
        public void setRemotePowerPlanHighPerformance(String MachineName)
        {
            String preferredPlan = String.Empty;
            try
            {
                RegistryKey baseKey = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, MachineName, RegistryView.Registry32);
                RegistryKey preferredPlanKey = baseKey.OpenSubKey(@"SOFTWARE\Microsoft\Windows\CurrentVersion\explorer\ControlPanel\NameSpace\{025A5937-A6BE-4686-A844-36FE4BEC8B6D}");
                preferredPlan = preferredPlanKey.GetValue("PreferredPlan").ToString();
                preferredPlanKey.SetValue("PreferredPlan", "8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c");
                
                preferredPlanKey.Close();
            }
            catch
            {
                RegistryKey baseKey = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, MachineName, RegistryView.Registry64);
                RegistryKey preferredPlanKey = baseKey.OpenSubKey(@"SOFTWARE\Microsoft\Windows\CurrentVersion\explorer\ControlPanel\NameSpace\{025A5937-A6BE-4686-A844-36FE4BEC8B6D}");
                preferredPlan = preferredPlanKey.GetValue("PreferredPlan").ToString();
                preferredPlanKey.SetValue("PreferredPlan", "8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c");

                preferredPlanKey.Close();
            }
        }
        #endregion
        #region LanguageHelpers
        [WebMethod]
        public List<String> GetControlIdentifiers()
        {
            List<String> controlIdentifiers = new List<String>();

            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "Select Distinct ControlName From ControlIdentifiers";

                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    controlIdentifiers.Add(reader["ControlName"].ToString());
                }
            }
            catch { }
            finally { connection.Close(); }

            return controlIdentifiers;
        }
        [WebMethod]
        public string[] GetControlIdentifierText(string controlIdentifierName, string language)
        {
            string[] retDict = new string[2];
            string controlIdentifierText = string.Empty;

            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "select Text from ControlsText\n" +
                                      "inner join Languages on ControlsText.LanguageID = Languages.ID\n" +
                                      "inner join ControlIdentifiers on ControlsText.ControlID = ControlIdentifiers.ID\n" +
                                      "where LanguageName = @LanguageName\n" +
                                      "and ControlName = @ControlName";

                command.Parameters.AddWithValue("@LanguageName", language);
                command.Parameters.AddWithValue("@ControlName", controlIdentifierName);

                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    controlIdentifierText = reader["Text"].ToString();
                    break;
                }
                retDict[0] = controlIdentifierName;
                retDict[1] = controlIdentifierText;
            }
            catch { }
            finally { connection.Close(); }

            return retDict;
        }
        [WebMethod]
        public string UpdateControlIdentifierText(string controlIdentifierName, string language, string text)
        {
            string controlIdentifierText = string.Empty;

            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            try
            {
                //Check to see if this control text is already defined for the given control and language
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "select ControlsText.ID from ControlsText\n" +
                                      "inner join Languages on ControlsText.LanguageID = Languages.ID\n" +
                                      "inner join ControlIdentifiers on ControlsText.ControlID = ControlIdentifiers.ID\n" +
                                      "where LanguageName = @LanguageName\n" +
                                      "and ControlName = @ControlName";

                command.Parameters.AddWithValue("@LanguageName", language);
                command.Parameters.AddWithValue("@ControlName", controlIdentifierName);

                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                // Control Text already defined do an update
                if (reader.HasRows)
                {
                    reader.Read();
                    string RecordID = reader[0].ToString();
                    reader.Close();

                    command = new SqlCommand();
                    command.CommandText = "Update ControlsText\n" +
                                          "SET Text=@Text\n" +
                                          "Where ID=@ID";
                    command.Connection = connection;
                    command.Parameters.AddWithValue("@Text", text);
                    command.Parameters.AddWithValue("@ID", RecordID);
                    
                    command.ExecuteNonQuery();
                }
                // Control Text not defined do an insert
                else
                {
                    reader.Close();
                    String controlID = String.Empty;
                    String languageID = String.Empty;

                    // Find controlIdentifier ID and Language ID
                    command = new SqlCommand();
                    command.Connection = connection;
                    command.CommandText = "select ControlIdentifiers.ID from ControlIdentifiers where ControlName = @ControlName";

                    command.Parameters.AddWithValue("@ControlName", controlIdentifierName);
                    reader = command.ExecuteReader();
                    if(reader.HasRows) 
                    {
                        reader.Read();
                        controlID = reader[0].ToString();
                        reader.Close();

                        command = new SqlCommand();
                        command.Connection = connection;
                        command.CommandText = "select ID from Languages where LanguageName = @LanguageName";

                        command.Parameters.AddWithValue("@LanguageName", language);
                        reader = command.ExecuteReader();
                        if (reader.HasRows)
                        {
                            reader.Read();
                            languageID = reader[0].ToString();
                            reader.Close();
                        }
                        else
                        {
                            throw new Exception("invald language");
                        }
                    }
                    else
                    {
                        throw new Exception("invald control");
                    }
                    command = new SqlCommand();
                    command.Connection = connection;
                    command.CommandText = "insert into ControlsText\n" +
                                          "(ID, ControlID, LanguageID, Text)\n" +
                                          "Values (NEWID(), @ControlID, @LanguageID, @Text)";

                    command.Parameters.AddWithValue("@ControlID", controlID);
                    command.Parameters.AddWithValue("@LanguageID", languageID);
                    command.Parameters.AddWithValue("@Text", text);
                    command.ExecuteNonQuery();
                }
            }
            catch { }
            finally { connection.Close(); }

            return controlIdentifierName;
        }
        #endregion
        #region WorkLog Methods
        [WebMethod]
        public List<WorkLogEntry> getRequestWorkLog(AuthorizationRequest req)
        {
            List<WorkLogEntry> workLog = new List<WorkLogEntry>();

            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "Select WorkLogID, ApprovalID, UserName, Note, " +
                                      "NoteDate\n" +
                                      "From WorkLog\n" +
                                      "WHERE ApprovalID=@RequestID";

                command.Parameters.AddWithValue("@RequestID", req.ApprovalID);

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    WorkLogEntry newEntry = new WorkLogEntry()
                    {
                        WorkLogID = reader["WorkLogID"].ToString(),
                        RequestID = reader["ApprovalID"].ToString(),
                        UserName = reader["UserName"].ToString(),
                        Note = reader["Note"].ToString(),
                        NoteDate = (DateTime)reader["NoteDate"]
                    };

                    if (newEntry.Note.Contains('\n')) { newEntry.displayLine = newEntry.Note.Substring(0, newEntry.Note.IndexOf('\n')); }
                    else { newEntry.displayLine = newEntry.Note; }
                    workLog.Add(newEntry);
                }
            }
            catch { }
            finally { connection.Close(); }

            return workLog;
        }
        [WebMethod]
        public void saveWorkLogEntry(WorkLogEntry entry)
        {
            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "Update WorkLog\n" +
                                      "SET RequestID=@RequestID, UserName=@UserName, " +
                                      "Note=@Note, NoteDate = @NoteDate\n" +
                                      "WHERE WorkLogID=@WorkLogID";

                command.Parameters.AddWithValue("@RequestID", entry.RequestID);
                command.Parameters.AddWithValue("@UserName", entry.UserName);
                command.Parameters.AddWithValue("@Note", entry.Note);
                command.Parameters.AddWithValue("@NoteDate", entry.NoteDate);
                command.Parameters.AddWithValue("@WorkLogID", entry.WorkLogID);

                command.ExecuteNonQuery();
            }
            catch { }
            finally { connection.Close(); }

            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "Update ApprovalTable" +
                                      "SET LastUpdated=@LastUpdated, LastUpdateUser=@UserName\n" +
                                      "WHERE ApprovalRequestID=@RequestID";

                command.Parameters.AddWithValue("@RequestID", entry.RequestID);
                command.Parameters.AddWithValue("@UserName", entry.UserName);
                command.Parameters.AddWithValue("@LastUpdated", DateTime.UtcNow);

                command.ExecuteNonQuery();
            }
            catch { }
            finally { connection.Close(); }
        }
        [WebMethod]
        public void DeleteWorkLogEntry(WorkLogEntry entry)
        {
            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);

            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "Delete From WorkLog\n" +
                                      "WHERE WorkLogID=@WorkLogID";

                command.Parameters.AddWithValue("@WorkLogID", entry.WorkLogID);

                command.ExecuteNonQuery();
            }
            catch { }
            finally { connection.Close(); }
        }
        [WebMethod]
        public WorkLogEntry createWorkLogEntry(String UserName, String RequestID, String Note, DateTime NoteDate)
        {
            SqlConnection connection = new SqlConnection(USER_IMAGING_SQL_CONNECTION_STRING);
            WorkLogEntry newWorkLogEntry = new WorkLogEntry()
            {
                WorkLogID = System.Guid.NewGuid().ToString(),
                UserName = UserName,
                RequestID = RequestID,
                Note = Note,
                NoteDate = NoteDate
            };

            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "Insert Into WorkLog " +
                                      "(WorkLogID, ApprovalID, UserName, Note, NoteDate)\n" +
                                      "Values (@WorkLogID, @RequestID, @UserName, @Note, @NoteDate)";

                command.Parameters.AddWithValue("@RequestID", newWorkLogEntry.RequestID);
                command.Parameters.AddWithValue("@UserName", newWorkLogEntry.UserName);
                command.Parameters.AddWithValue("@Note", newWorkLogEntry.Note);
                command.Parameters.AddWithValue("@NoteDate", newWorkLogEntry.NoteDate);
                command.Parameters.AddWithValue("@WorkLogID", newWorkLogEntry.WorkLogID);

                command.ExecuteNonQuery();
            }
            catch { }
            finally { connection.Close(); }

            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;

                command.CommandText = "Update ApprovalTable\n" +
                                      "SET LastUpdated=@LastUpdated, LastUpdateUser=@UserName\n" +
                                      "WHERE ApprovalRequestID=@RequestID";

                command.Parameters.AddWithValue("@RequestID", newWorkLogEntry.RequestID);
                command.Parameters.AddWithValue("@UserName", newWorkLogEntry.UserName);
                command.Parameters.AddWithValue("@LastUpdated", DateTime.UtcNow);

                command.ExecuteNonQuery();
            }
            catch { }
            finally { connection.Close(); }


            if (newWorkLogEntry.Note.Contains('\n')) { newWorkLogEntry.displayLine = newWorkLogEntry.Note.Substring(0, newWorkLogEntry.Note.IndexOf('\n')); }
            else { newWorkLogEntry.displayLine = newWorkLogEntry.Note; }

            return newWorkLogEntry;

        }
        #endregion
        #region helperFunctions
        private enum objectClass
        {
            user, group, computer
        }
        private enum returnType
        {
            distinguishedName, ObjectGUID
        }
        private string GetObjectDistinguishedName(objectClass objectCls, returnType returnValue, string objectName)
        {
            Forest currentForest = Forest.GetCurrentForest();
            DomainCollection domCollection = currentForest.Domains;
            string distinguishedName = string.Empty;

            foreach (Domain objDomain in domCollection)
            {
                string LdapDomain = objDomain.Name;
                string connectionPrefix = "LDAP://" + LdapDomain;
                DirectoryEntry entry = new DirectoryEntry(connectionPrefix);
                DirectorySearcher mySearcher = new DirectorySearcher(entry);

                switch (objectCls)
                {
                    case objectClass.user:
                        mySearcher.Filter = "(&(objectClass=user)(|(cn=" + objectName + ")(sAMAccountName=" + objectName + ")))";
                        break;
                    case objectClass.group:
                        mySearcher.Filter = "(&(objectClass=group)(|(cn=" + objectName + ")(dn=" + objectName + ")))";
                        break;
                    case objectClass.computer:
                        mySearcher.Filter = "(&(objectClass=computer)(|(cn=" + objectName + ")(dn=" + objectName + ")))";
                        break;
                }
                SearchResult result = mySearcher.FindOne();

                if (result != null)
                {
                    DirectoryEntry directoryObject = result.GetDirectoryEntry();
                    if (returnValue.Equals(returnType.distinguishedName))
                    {
                        distinguishedName = "LDAP://" + directoryObject.Properties
                            ["distinguishedName"].Value;
                    }
                    if (returnValue.Equals(returnType.ObjectGUID))
                    {
                        distinguishedName = directoryObject.Guid.ToString();
                    }
                    entry.Close();
                    entry.Dispose();
                    mySearcher.Dispose();
                    break;
                }
                else
                {
                    entry.Close();
                    entry.Dispose();
                    mySearcher.Dispose();
                }
            }
            return distinguishedName;
        }
        private ArrayList AttributeValuesMultiString(string attributeName,string objectDn, ArrayList valuesCollection, bool recursive)
        {
            PropertyValueCollection ValueCollection;
            DirectoryEntry ent = new DirectoryEntry(objectDn);
            try
            {
                ValueCollection = ent.Properties[attributeName];
                IEnumerator en = ValueCollection.GetEnumerator();

                while (en.MoveNext())
                {
                    if (en.Current != null)
                    {
                        if (!valuesCollection.Contains(en.Current.ToString()))
                        {
                            valuesCollection.Add(en.Current.ToString());
                            if (recursive)
                            {
                                AttributeValuesMultiString(attributeName, "LDAP://" +
                                en.Current.ToString(), valuesCollection, true);
                            }
                        }
                    }
                }
            }
            catch { }
            finally
            {
                ent.Close();
                ent.Dispose();
            }
            return valuesCollection;
        }
        private ArrayList getGroups(string userDn, bool recursive)
        {
            ArrayList groupMemberships = new ArrayList();
            return AttributeValuesMultiString("memberOf", userDn, groupMemberships, recursive);
        }
        private string AttributeValuesSingleString(string attributeName, string objectDn)
        {
            try
            {
                string strValue;
                DirectoryEntry ent = new DirectoryEntry(objectDn);
                strValue = ent.Properties[attributeName].Value.ToString();
                ent.Close();
                ent.Dispose();
                return strValue;
            }
            catch
            {
                return "Not Found";
            }
        }
        private string getComputerNameRegistry(String ipAddr)
        {
            string computerName = "Not Found";
            RegistryKey baseKey = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, ipAddr, RegistryView.Registry32);

            try
            {
                RegistryKey computerNameKey = baseKey.OpenSubKey(@"SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName");
                computerName = computerNameKey.GetValue("ComputerName").ToString();
                computerNameKey.Close();
            }
            catch 
            {
                try
                {
                    baseKey = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, ipAddr, RegistryView.Registry64);
                    RegistryKey computerNameKey = baseKey.OpenSubKey(@"SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName");
                    computerName = computerNameKey.GetValue("ComputerName").ToString();
                    computerNameKey.Close();
                }
                catch { }
            }
            finally
            {
                baseKey.Close();
            }

            return computerName;
        }
        [WebMethod]
        public String getUserFriendlyName(string userID, string userDomain)
        {
            String userName = String.Empty;

            String distinguishedName = string.Empty;
            string connectionPrefix = "LDAP://" + userDomain;
            DirectoryEntry entry = new DirectoryEntry(connectionPrefix);
            DirectorySearcher mySearcher = new DirectorySearcher(entry);

            mySearcher.Filter = "(&(objectClass=user)(|(cn=" + userID + ")(sAMAccountName=" + userID + ")))";
            SearchResult result = mySearcher.FindOne();

            if (result != null)
            {
                DirectoryEntry directoryObject = result.GetDirectoryEntry();
                userName = directoryObject.Name.Split('=')[1].ToString();
            }

            return userName;
        }
        #endregion

    }
}
